set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions=200;
set hive.exec.max.dynamic.partitions.pernode=200;

with false_taking as (
    select
        taking.dt
        , taking.receive_network_code
        , taking.receive_network_name
        , sum(case when taking.order_source_code = '七星潭' then 1 else 0 end) as qxt_false_taking_cnt
        , sum(case when taking.order_source_code = '桃花岛' then 1 else 0 end) as thd_false_taking_cnt
        , sum(case when taking.order_source_code = '逍遥峰' then 1 else 0 end) as xyf_false_taking_cnt
        , sum(case when taking.order_source_code = '紫金山' then 1 else 0 end) as zjs_false_taking_cnt
        , sum(case when taking.order_source_code = '极地湾' then 1 else 0 end) as jdw_false_taking_cnt
        , count(*)                                                            as false_taking_cnt
    from
        jms_dm.dm_waybill_false_taking_dt taking
    left join jms_dim.dim_network_abnormal_type_union_dt dim -- 异常网点剔除
            on  dim.dt between date_sub('{{ execution_date | cst_ds }}',7) and '{{ execution_date | cst_ds }}'
            and taking.dt = dim.dt
            and taking.receive_network_code = dim.network_code
    where
        taking.dt between date_sub('{{ execution_date | cst_ds }}',7) and '{{ execution_date | cst_ds }}'
        and dim.network_code is null
    group by
        taking.dt
        ,taking.receive_network_code
        ,taking.receive_network_name
),
taking_agg as (
    select
        dt
        , taking_date
        , receive_network_code
        , receive_network_name
        , receive_proxy_code
        , receive_proxy
        , sum(should_hand_over_num)   as receive_network_should_hand_in_cnt
        , sum(case when order_source in ('七星潭', '桃花岛', '逍遥峰', '紫金山')  then should_hand_over_num end )  as receive_network_platform_should_hand_in_cnt
        , sum(case when order_source = '七星潭' then should_hand_over_num else 0 end) as network_qxt_should_hand_in_cnt
        , sum(case when order_source = '桃花岛' then should_hand_over_num else 0 end) as network_thd_should_hand_in_cnt
        , sum(case when order_source = '逍遥峰' then should_hand_over_num else 0 end) as network_xyf_should_hand_in_cnt
        , sum(case when order_source = '紫金山' then should_hand_over_num else 0 end) as network_zjs_should_hand_in_cnt
        , sum(case when order_source = '极地湾' then should_hand_over_num else 0 end) as network_jdw_should_hand_in_cnt
    from
        jms_dm.dm_waybill_collect_intime_taking_agg
    where
        dt between date_sub('{{ execution_date | cst_ds }}',7) and '{{ execution_date | cst_ds }}'
    group by
        dt
        , taking_date
        , receive_network_code
        , receive_network_name
        , receive_proxy_code
        , receive_proxy
)
insert overwrite table jms_dm.dm_waybill_false_taking_network_dt partition (dt)
select
    taking_agg.taking_date
    , taking_agg.receive_network_code
    , taking_agg.receive_network_name
    , taking_agg.receive_proxy_code             as receive_agent_code
    , taking_agg.receive_proxy                  as receive_agent_name
    , dim_network.virt_code                     as receive_virt_agent_code
    , dim_network.virt_name                     as receive_virt_agent_name
    , false_taking.qxt_false_taking_cnt                        -- 七星潭虚假揽收票数
    , false_taking.thd_false_taking_cnt                        -- 桃花岛虚假揽收票数
    , false_taking.xyf_false_taking_cnt                        -- 逍遥峰虚假揽收票数
    , false_taking.zjs_false_taking_cnt                        -- 紫金山虚假揽收票数
    , false_taking.false_taking_cnt                            -- 网点全部虚假揽收票数
    , taking_agg.receive_network_should_hand_in_cnt            -- 网点全部应交件票数
    , taking_agg.receive_network_platform_should_hand_in_cnt   -- 网点四大平台应交件票数
    , taking_agg.network_qxt_should_hand_in_cnt                -- 七星潭应交件票数
    , taking_agg.network_thd_should_hand_in_cnt                -- 桃花岛应交件票数
    , taking_agg.network_xyf_should_hand_in_cnt                -- 逍遥峰应交件票数
    , taking_agg.network_zjs_should_hand_in_cnt                -- 紫金山应交件票数
    , dim_network.area_code
    , dim_network.area_name
    , false_taking.jdw_false_taking_cnt                        -- 极地湾虚假揽收票数
    , taking_agg.network_jdw_should_hand_in_cnt                -- 极地湾应交件票数
    , taking_agg.dt
from
    taking_agg
left join
    false_taking
        on  taking_agg.receive_network_code = false_taking.receive_network_code
        and taking_agg.dt = false_taking.dt
left join
    jms_dim.dim_network_whole_massage  dim_network
        on taking_agg.receive_network_code = dim_network.code
distribute by dt
;